The present invention relates to updating a database and more particularly, but not by way of limitation, to a system and methods for updating the structure of a database without restricting a user's access to the database during the update operation.
Databases may be characterized as comprising two types of “objects”—data objects and index objects, both of which are typically embodied as files stored on one or more direct access storage devices (DASD). Data objects and index objects are, in turn, organized and managed through a system catalog or data dictionary (also embodied as files stored on one or more DASD). A system catalog's function is to describe the objects in its database. For example, a system catalog identifies the structure (schema) of each table in its database and any indices associated with those tables. For ease of discussion the following disclosure uses the term ‘table’ to refer to data objects, the term ‘index’ to refer to index objects and the term ‘catalog’ to refer to a database's system catalog.
Referring to FIG. 1, a typical prior art database change operation 100 is shown. To start, a user-initiated database change command is received (block 105). Illustrative change commands include those commands directed to inserting a new database table entry, updating an existing database table entry, creating an index and updating or rebalancing an existing index. Once received, the change command is executed (block 110) and committed (block 115). As one of ordinary skill in the art would recognize, the commit operation makes permanent the database changes made during the acts of block 110. Next, the update process contemplated by the change command of block 105 and enabled by the acts of blocks 110 and 115 is performed (block 120). In some database change operations, either the entire database or that portion of the database being updated is restricted from user access (block 125). Once access is blocked, the database (e.g., tables and/or indices) are updated in accordance with the change command (block 130). Following completion of the update process, user access is restored (block 135) and normal operations against the updated database may continue (block 140). It is important to note that user access to at least a portion of the database being updated is blocked during the acts of block 120—often referred to as an “outage.”
By way of example, consider index rebalance operation 200 outlined in FIG. 2. In this illustrative prior art database change operation, the user wishes to rebalance a partitioned database by changing the key range (sometimes referred to as the Limitkey value) associated with one or more partitions of the target database. One illustrative database which allows this type of change operation is the DB2® database. (DB2 is a registered trademark of the International Business Machines corporation of Armonk, N.Y.) After receiving the user's ALTER command (block 105), where ALTER is the DB2 command to effect the desired change in Limitkey values, rebalance process 200 locks the target database (block 205) so that subsequent user queries and/or commands are queued for later execution. The catalog for the target database is then updated to reflect the desired Limitkey changes (block 210), the partitions that must be updated to effect the desired changes are set to a restricted state (block 215) and the lock set during the acts of block 205 is released (block 220). The catalog changes made during the acts of block 210 and the restricted status of one or more of the database's partitions are made permanent when committed (block 115).
Next, rebalance process 200 issues a STOP command against the target dataset, or at least those partitions of the target database placed into a restricted state in accordance with block 215 (block 225). Until the restricted status is removed and a start command is issued (see block 240 below), those partitions placed into a restricted state are not accessible to users and any queries and/or commands that require the restricted partitions are failed. Accordingly, a user outage begins once the change command is committed. Those partitions (tables and indices) needing modifications to effect the desired rebalance operation are made (block 230), the restricted state of the modified partitions is released (block 235) and the database restarted (240). Once restarted, user access is restored (i.e., the blockage ends) and normal user queries and/or commands may be processed.
As noted above, some database changes, such as the index rebalance operation of FIG. 2, cause an outage during which user access to at least a portion of a target database is not possible. To mitigate some of the problems attendant with an outage, database administrators typically schedule such operations for early in the day and/or weekends when the number of expected users is low. However, as the economy evolves into a 24-hours a day operation, an outage at any time of the day can have serious and negative business consequences. Thus, it would be beneficial to provide techniques (methods and devices) to effect structural database changes that do not create or cause a user outage.